﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAO
{
    public class ItemService
    {
        private String CMD;

        /// <summary>
        ///     通过指定用户ID得到该用户所有分组信息
        /// </summary>
        /// <param name="UserId" type="string">
        ///     <para>
        ///         用户ID
        ///     </para>
        /// </param>
        /// <returns>
        ///     返回null , 则表示不存在该用户
        /// </returns>
        public DataTable GetItemByUserId(String UserId)
        {
            CMD = "SELECT * FROM TB_ITEM WHERE Item_UserId=" + UserId;
            SqlDataAdapter Adapter = new SqlDataAdapter(CMD, DB.DB_CONN);
            using(DataSet DS = new DataSet("DS_UserItem"))
            {
                Adapter.Fill(DS, "TB_ITEMINFO");
                return DS.Tables["TB_ITEMINFO"];
            }
        }

        /// <summary>
        ///     根据指定分组ID, 更新用户分组名
        /// </summary>
        /// <param name="ItemName">新的分组名</param>
        /// <param name="ItemId">分组ID</param>
        /// <returns></returns>
        public bool UpdateItem(String ItemName, int ItemId)
        {
            CMD = "UPDATE TB_ITEM SET ItemName='" + ItemName + "' WHERE ItemId=" + ItemId;
            using(SqlCommand Command = new SqlCommand(CMD, DB.DB_CONN))
            {
                DB.DB_CONN.Open();
                if(Command.ExecuteNonQuery() > 0)
                {
                    DB.CloseDB();
                    return true;
                }
                DB.CloseDB();
                return false;
            }
        }

        /// <summary>
        ///     添加新的分组到指定用户Id分组集合下
        /// </summary>
        /// <param name="UserId">用户Id</param>
        /// <param name="ItemName">新分组名</param>
        /// <returns>返回-1 , 表示添加失败</returns>
        public int AddItem(int UserId, String ItemName)
        {
            using (SqlCommand Command = new SqlCommand(CMD, DB.DB_CONN))
            {
                DB.DB_CONN.Open();
                using(SqlCommand command = new SqlCommand())
                {
                    int Result = 0;
                    command.Connection = DB.DB_CONN;
                    command.CommandText = "PROC_ADDITEM";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.Add("UserId", UserId);
                    command.Parameters.Add("ItemName", ItemName);
                    command.Parameters.Add("ItemId", Result);
                    Result = int.Parse(command.ExecuteScalar().ToString());
                    if (Result > 0)
                    {
                        DB.CloseDB();
                        return Result;
                    }
                }
                DB.CloseDB();
                return -1;
            }
        }

        /// <summary>
        ///     删除指定分组ID删除
        /// </summary>
        /// <param name="ItemId">分组ID</param>
        /// <returns>返回false , 则表示删除失败</returns>
        public bool DeleteItem(int ItemId)
        {
            CMD = "DELETE FROM TB_ITEM WHERE ItemId=" + ItemId;
            using (SqlCommand Command = new SqlCommand(CMD, DB.DB_CONN))
            {
                DB.DB_CONN.Open();
                if (Command.ExecuteNonQuery() > 0)
                {
                    DB.CloseDB();
                    return true;
                }
                DB.CloseDB();
                return false;
            }
        }

        /// <summary>
        ///     通过用户ID得到该用户的所有分组ID
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <returns>返回该用户对应的所有分组ID</returns>
        public int[] GetItemsByUserId(int UserId)
        {
            int[] Items = null;
            CMD = "SELECT ItemId FROM TB_ITEM WHERE Item_UserId=" + UserId;
            SqlDataAdapter Adapter = new SqlDataAdapter(CMD, DB.DB_CONN);
            using (DataSet DS = new DataSet("TB_ITEMINFO_BY_USERID"))
            {
                Adapter.Fill(DS, "TB_ITEMINFO_BY_USERID");
                Items = new int[DS.Tables[0].Rows.Count];
                int Index = 0;
                foreach (DataTable dt in DS.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        Items.SetValue(int.Parse(dr["ItemId"].ToString()), Index);
                        Index++;
                    }
                }
            }
            return Items;
        }
    }
}
